import logging
import math
import time
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

sample_class_router = APIRouter(prefix="/sampleClass", tags=["样品分类管理"], dependencies=[Depends(oauth2_scheme)])


class SampleCategorySearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fsamplename: Optional[str] = Field("", description="分类名称(支持前缀匹配)")
    fclassstno: Optional[str] = Field("", description="父级分类编号")


@sample_class_router.get("/list", summary="样品分类查询接口")
def list_sample_class(search: SampleCategorySearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from q_ts_sample_class where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fsamplename != "":
        sql = sql + " and fclassstname like concat('%', :fsamplename ,'%')"
        param["fsamplename"] = search.fsamplename
    if search.fclassstno != "":
        sql = sql + " and fparentno = :fparentno "
        param["fparentno"] = search.fclassstno
    sql = sql + " limit :limit offset :offset "
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fsite_no,fclassstno,fclassstname,fparentno,fclassstyle,flevel,"
    sql = sql + " feffday,fupddate,foperator"
    sql = sql + " from q_ts_sample_class where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fsamplename != "":
        sql = sql + " and fclassstname like concat('%', :fsamplename ,'%')"
        param["fsamplename"] = search.fsamplename
    if search.fclassstno != "":
        sql = sql + " and fparentno = :fparentno "
        param["fparentno"] = search.fclassstno
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()
    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['fclassstno'] = data[key][1]
        d1['fclassstname'] = data[key][2]
        d1['fparentno'] = data[key][3]
        d1['fclassstyle'] = data[key][4]
        d1['flevel'] = data[key][5]
        d1['feffday'] = data[key][6]
        d1['fupddate'] = data[key][7]
        d1['foperator'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class SampleCategoryCreate(BaseModel):
    """样品分类新增请求参数"""
    fclassstno: str = Field(..., description="分类编号")
    fclassstname: str = Field(..., description="分类名称")
    fsiteNo: str = Field(..., description="站点编号")
    fparentno: Optional[str] = Field("", description="父级分类编号，默认为空")
    fclassstyle: Optional[str] = Field("0", description="分类类型，默认0")
    feffday: Optional[str] = Field("0", description="有效天数，默认0")
    flevel: Optional[str] = Field("0", description="分类级别，默认0")


@sample_class_router.post("/create", summary="样品分类新增接口")
def create_sample_class(create: SampleCategoryCreate, db: Session = Depends(get_db),
                        token: str = Depends(oauth2_scheme)):
    try:
        fclassstno = create.fclassstno
        fclassstname = create.fclassstname
        fsiteNo = create.fsiteNo
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fclassstno from q_ts_sample_class where fclassstno= :fclassstno and fsite_No= :fsiteNo "
            data = db.execute(text(sql), {
                "fclassstno": fclassstno,
                "fsiteNo": fsiteNo,
            }).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="分类编号已存在", code=506)
            else:
                sql = "insert into q_ts_sample_class(fsite_no,fclassstno,fclassstname,fparentno,fclassstyle,flevel,feffday,fupddate,foperator)"
                sql = sql + " values( :fsiteNo , :fclassstno , :fclassstname , :fparentno , :fclassstyle , :flevel , :feffday ,now(), :userid );"
                db.execute(text(sql), {
                    "fsiteNo": fsiteNo,
                    "fclassstno": fclassstno,
                    "fclassstname": fclassstname,
                    "fparentno": create.fparentno,
                    "fclassstyle": create.fclassstyle,
                    "flevel": create.flevel,
                    "feffday": create.feffday,
                    "userid": user_id,
                })
                # 返回
                current_datetime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                d1 = {}
                d1['fsiteNo'] = fsiteNo
                d1['fclassstno'] = fclassstno
                d1['fclassstname'] = fclassstname
                d1['fparentno'] = create.fparentno
                d1['fclassstyle'] = create.fclassstyle
                d1['flevel'] = create.flevel
                d1['feffday'] = create.feffday
                d1['fupddate'] = current_datetime
                d1['foperator'] = user_id
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@sample_class_router.post("/update", summary="样品分类修改接口")
def update_sample_calss(update: SampleCategoryCreate, db: Session = Depends(get_db),
                        token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update q_ts_sample_class"
            sql = sql + " set fclassstname= :fclassstname ,fparentno= :fparentno "
            sql = sql + " ,fupddate=now(),foperator= :userid "
            sql = sql + " where fclassstno= :fclassstno  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fclassstname": update.fclassstname,
                "fparentno": update.fparentno,
                "fclassstno": update.fclassstno,
                "fsiteNo": update.fsiteNo,
                "userid": user_id,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@sample_class_router.post("/delete/{fclassstno}", summary="样品分类删除接口")
def delete_sample_class(fclassstno: str = Path(description="分类编号"), db: Session = Depends(get_db)):
    try:
        param = {"fclassstno": fclassstno}
        with db.begin():
            sql = "select fsite_no from q_ts_sample_class where fparentno= :fclassstno "
            data1 = db.execute(text(sql), param).fetchall()

            if len(data1) > 0:
                return BaseResponse.error(message="该分类有下级分类")
            else:
                sql = "select fsite_no from q_ts_sample_info where fclassstno= :fclassstno "
                data2 = db.execute(text(sql), param).fetchall()

                if len(data2) > 0:
                    return BaseResponse.error(message="该分类有物料")
                else:
                    sql = "delete from q_ts_sample_class where fclassstno= :fclassstno "
                    db.execute(text(sql), param)
                    return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
